import keyword

from mpmath import limit
from rest_framework.permissions import IsAuthenticated
from rest_framework.views import APIView
from rest_framework.response import Response
import pymysql
from elasticsearch import Elasticsearch
from elasticsearch.helpers import bulk

# 数据库和ES的配置（本地开发环境）
DB_CONFIG = {
    "host": "rm-2ze87yxlw6za1lf09ao.mysql.rds.aliyuncs.com",
    "port": 3306,
    "user": "boss_feng",
    "password": "boss_feng123",
    "db": "boss",
    # "charset": "utf8mb4"
}

# CACHES = {
#     "default": {
#         "BACKEND": "django_redis.cache.RedisCache",
#         "LOCATION": "redis://:hzs123456:Hzs123456@r-2ze10pmmeg0trjkx21pd.redis.rds.aliyuncs.com/0",  # Redis地址和数据库编号
#         "OPTIONS": {
#             "CLIENT_CLASS": "django_redis.client.DefaultClient",
#         }
#     }
# }

CACHES = {
    "default": {
        "BACKEND": "django_redis.cache.RedisCache",
        "LOCATION": "redis://127.0.0.1:6379/0",  # 本地Redis默认地址，数据库0
        "OPTIONS": {
            "CLIENT_CLASS": "django_redis.client.DefaultClient",
        }
    }
}

# ES连接（添加超时配置）
ES = Elasticsearch(
    ["http://localhost:9200"],
    timeout=30,  # 连接超时30秒
    max_retries=1,
    retry_on_timeout=False
)

table__name = 'category_category'
es_index = f"{table__name}_index"

# 表名和索引的映射配置
TABLE_INDEX_MAPPING = {
    'category_category': 'category_category_index',
    'job': 'job_index',
    'ccompany': 'company_index',
}


# 同步数据到es（通用版本，支持多个表）
class SimpleSyncView(APIView):
    def get(self, request):
        # 获取要同步的表名（默认为 category_category）
        table_name = request.query_params.get('table', table__name)
        
        # 获取对应的 ES 索引名
        index_name = TABLE_INDEX_MAPPING.get(table_name, f"{table_name}_index")
        
        conn = None
        cursor = None
        try:
            # 建立数据库连接并查询
            conn = pymysql.connect(**DB_CONFIG)
            cursor = conn.cursor()
            
            # 检查表是否存在
            cursor.execute(f"SHOW TABLES LIKE '{table_name}';")
            if not cursor.fetchone():
                return Response({
                    'code': 404,
                    'message': f'表 {table_name} 不存在'
                })
            
            sql = f'SELECT * FROM `{table_name}`'
            cursor.execute(sql)
            sql_data = cursor.fetchall()
            sql_data_count = len(sql_data)
            print(f"成功获取表 {table_name} 的数据，共 {sql_data_count} 条")

            # 同步es
            sync_result = self.sync_to_es(sql_data, cursor.description, index_name)

            return Response({
                'code': 200,
                "message": f"成功获取表 {table_name} 的数据，共 {sql_data_count} 条，已同步 {sync_result['success']} 条到ES（索引：{index_name}）",
            })
        except Exception as e:
            print(f"报错信息: {e}")
            return Response({
                'code': 500,
                'message': f'报错信息：{e}'
            })
        finally:
            # 关闭数据库连接和游标
            if cursor:
                cursor.close()
            if conn:
                conn.close()

    def sync_to_es(self, sql_data, description, index_name):
        # 提取字段名
        fields = [field[0] for field in description]
        actions = []

        for row in sql_data:
            # 元组转字典（字段名映射）
            doc = dict(zip(fields, row))

            # 基础数据清洗（只保留关键处理）
            cleaned_doc = {}
            for key, value in doc.items():
                if isinstance(value, str):
                    # 仅处理单引号和换行符（核心冲突字符）
                    cleaned_value = value.replace("'", "\"").replace("\n", " ").replace("\r", "")
                    cleaned_doc[key] = cleaned_value
                else:
                    cleaned_doc[key] = value  # 非字符串直接保留

            # 构建ES批量操作格式
            actions.append({
                "_index": index_name,
                "_source": cleaned_doc
            })

        if not actions:
            return {"success": 0, "failed": 0}

        # 索引不存在则创建
        if not ES.indices.exists(index=index_name):
            ES.indices.create(index=index_name)

        # 批量写入ES
        success, failed = bulk(ES, actions)
        return {"success": success, "failed": len(failed) if failed else 0}


class ESIndexView(APIView):
    def get(self, request):
        user_id = 1
        index_name = request.query_params.get('index', '')  # 要查询的索引名
        page = int(request.query_params.get('page', 1))  # 页码，默认第1页
        size = int(request.query_params.get('size', 10))  # 每页条数，默认10条
        keyword = request.query_params.get('keyword', '').strip()  # 搜索关键字（去除首尾空格）

        # 校验索引名
        if not index_name:
            return Response({
                'code': 400,
                'message': 'es索引名不能为空'
            })

        try:
            # 检查 ES 连接
            try:
                if not ES.ping():
                    return Response({
                        'code': 500,
                        'message': 'Elasticsearch服务未启动或连接失败'
                    })
            except Exception as es_error:
                print(f"ES连接失败: {str(es_error)}")
                return Response({
                    'code': 500,
                    'message': 'Elasticsearch服务未启动，请先启动ES服务'
                })
            # 检查索引是否存在
            if not ES.indices.exists(index=index_name):
                return Response({
                    'code': 404,
                    'message': f"索引 '{index_name}' 不存在"
                })

            # 构建基础查询体
            query_body = {
                "query": {
                    "match_all": {}  # 默认查询所有文档
                }
            }

            # 如果有关键词，根据不同索引搜索不同字段
            if keyword:
                # 根据索引名称确定搜索字段
                if 'job' in index_name.lower():
                    # 职位索引：搜索 title（职位标题）、description（描述）、requirements（要求）
                    query_body["query"] = {
                        "multi_match": {
                            "query": keyword,
                            "fields": ["title^3", "description", "requirements", "benefits"],  # title权重最高
                            "type": "best_fields"
                        }
                    }
                elif 'company' in index_name.lower():
                    # 公司索引：搜索 name（公司名称）、short_name、description
                    query_body["query"] = {
                        "multi_match": {
                            "query": keyword,
                            "fields": ["name^3", "short_name^2", "description"],
                            "type": "best_fields"
                        }
                    }
                else:
                    # 默认（分类等）：搜索 name 或 title 字段
                    query_body["query"] = {
                        "multi_match": {
                            "query": keyword,
                            "fields": ["name", "title"],
                            "type": "best_fields"
                        }
                    }
                
                # 保存搜索历史到Redis（如果Redis可用）
                try:
                    import redis
                    r = redis.Redis(host='127.0.0.1', port=6379, db=0, socket_connect_timeout=1)
                    r.ping()
                    search_rusult = f'boss:search:{user_id}:{index_name}'
                    r.lpush(search_rusult, keyword)
                except:
                    pass  # Redis不可用时忽略

            # 执行ES查询（带分页）
            result = ES.search(
                index=index_name,
                body=query_body,
                from_=(page - 1) * size,  # 分页偏移量（第N页从(N-1)*size开始）
                size=size,
                track_total_hits=True  # 确保返回准确的总条数
            )

            # 解析结果
            total = result["hits"]["total"]["value"]  # 总文档数
            docs = [
                {
                    "id": hit["_id"],  # 文档ID
                    "data": hit["_source"]  # 文档原始数据
                }
                for hit in result["hits"]["hits"]
            ]

            # 计算总页数
            total_pages = (total + size - 1) // size if total > 0 else 1

            # 返回响应
            return Response({
                'code': 200,
                'message': '查询成功',
                'data': {
                    "index": index_name,
                    "total": total,
                    "page": page,
                    "size": size,
                    "total_pages": total_pages,
                    "docs": docs
                }
            })

        except Exception as e:
            return Response({
                'code': 500,
                'message': f'查询失败，错误信息：{e}'
            })


class query_view(APIView):
    def get(self, request):
        try:
            user_id = 1
            index_name = request.query_params.get('index', '')
            import redis
            try:
                r = redis.Redis(host='127.0.0.1', port=6379, db=0, socket_connect_timeout=2)
                r.ping()  # 测试连接
            except redis.ConnectionError:
                return Response({
                    'code': 200,
                    'message': 'Redis服务未启动，搜索历史功能不可用',
                    'data': []
                })
            search_rusult = f'boss:search:{user_id}:{index_name}'
            data = r.lrange(search_rusult, 0, -1)
            print(data)
            return Response({
                'code': 200,
                'message': '查询成功',
                'data': data
            })
        except Exception as e:
            print(f"查询搜索历史失败: {str(e)}")
            return Response({
                'code': 200,
                'message': '搜索历史加载失败',
                'data': []
            })


class query_del(APIView):
    def delete(self, request):
        try:
            user_id = 1
            index_name = request.query_params.get('index', '')
            import redis
            r = redis.Redis(host='127.0.0.1', port=6379, db=0)
            search_rusult = f'boss:search:{user_id}:{index_name}'
            r.delete(search_rusult)
            return Response({
                'code': 200,
                'message': '删除成功',
            })
        except Exception as e:
            return Response({
                'code': 500,
                'message': f'删除失败，错误信息：{str(e)}'
            })